#include "adminwidget.h"
#include "ui_adminwidget.h"

AdminWidget::AdminWidget(QWidget *parent) :
    QTabWidget(parent),
    ui(new Ui::AdminWidget)
{
    ui->setupUi(this);

    QSettings s;
    tactile = s.value("tactile").toString() == "OUI" ? true : false;
    gestionStocks = s.value("gestionstock").toString() == "OUI" ? true : false;

    ui->checkAdminTactile->setChecked(tactile);
    ui->checkAdminStock->setChecked(gestionStocks);


    ui->lineConnexionHost->setText(s.value("connexion/hostname").toString());
    ui->lineConnexionBase->setText(s.value("connexion/dbname").toString());
    ui->lineConnexionUser->setText(s.value("connexion/user").toString());
    ui->lineConnexionPw->setText(s.value("connexion/pw").toString());


    if(tactile){
        clavierConsos = new Clavier(true, this);
        clavierPg = new Clavier(true, this);
        clavierAdmin = new Clavier(true, this);

        padAdmin = new ClavNum(true, this);
        padConsos = new ClavNum(true, this);
        padPg = new ClavNum(true, this);
        padRA = new ClavNum(true, this);

        ui->layoutConsosClavier->addWidget(clavierConsos);
        ui->layoutPgClavier->addWidget(clavierPg);
        ui->groupBoxPadAdmin->layout()->addWidget(padAdmin);
        ui->layoutConsosPad->addWidget(padConsos);
        ui->layoutPgPad->addWidget(padPg);
        ui->layoutRAPad->addWidget(padRA);
        ui->groupBoxMessage->layout()->addWidget(clavierAdmin);


        padPg->show();
        padRA->show();
        padAdmin->show();
        padConsos->show();
        clavierConsos->show();
        clavierPg->show();
        clavierAdmin->show();

    }

    ui->groupPgCreer->hide();
    ui->groupConsosStat->hide();
    ui->groupAgiosAjouter->hide();
    ui->groupRemiseSession->hide();

    modelPgHistorique = new QSqlQueryModel(this);

    QStringList remiseHeaderLabels;
    remiseHeaderLabels << "Fam's" << "Prom's" << "Chèque" << "Montant";
    ui->tableSessionRemise->setHorizontalHeaderLabels(remiseHeaderLabels);

    db = QSqlDatabase::database();

    modelRemise = new QSqlQueryModel(this);
    ui->tableRemises->setModel(modelRemise);
    majListeRemise();

    modelAgios = new QSqlQueryModel(this);
    ui->tableAgiosInfo->setModel(modelAgios);
    majListAgios();

    modelAgiosProms = new QSqlQueryModel(this);
    ui->listAgiosProms->setModel(modelAgiosProms);
    majAgiosProms();

    modelAgiosPgs = new QSqlQueryModel(this);
    ui->tableAgiosPgs->setModel(modelAgiosPgs);
    majAgiosPgs();

    modelConsos = new QSqlTableModel(this);
    modelConsos->setTable("Consos");
    modelConsos->setEditStrategy(QSqlTableModel::OnManualSubmit);
    modelConsos->setFilter("Actif = 1");
    modelConsos->sort(1,Qt::AscendingOrder);
    modelConsos->setHeaderData(1, Qt::Horizontal, tr("Intitule"));
    modelConsos->setHeaderData(4, Qt::Horizontal, tr("Prix"));
    ui->listConsos->setModel(modelConsos);
    ui->listConsos->hideColumn(0);
    ui->listConsos->hideColumn(2);
    ui->listConsos->hideColumn(3);
    ui->listConsos->hideColumn(5);
    ui->listConsos->hideColumn(6);

    idConsoCourante = 0;

    scene = new QGraphicsScene(this);
    ui->graphicsView->setScene(scene);

    dernierModifier = NULL;
    majDuree();


    ui->plainTextEdit->setPlainText(s.value("message").toString());

    ui->listAdminDispo->installEventFilter(this);
    ui->listAdminDroite->installEventFilter(this);
    ui->listAdminGauche->installEventFilter(this);

    QStringList prefsBlocDroite = s.value("prefBlocDroite").toString().split(";");
    QStringList prefsBlocGauche = s.value("prefBlocGauche").toString().split(";");
    ui->listAdminDroite->addItems(prefsBlocDroite);
    ui->listAdminGauche->addItems(prefsBlocGauche);

    QStringList l = ModuleAccueil::modules();

    for(int i=0; i< prefsBlocDroite.size(); i++){
        l.removeAll(prefsBlocDroite.at(i));
    }
    for(int i=0; i< prefsBlocGauche.size(); i++){
        l.removeAll(prefsBlocGauche.at(i));
    }

    ui->listAdminDispo->addItems(l);


    if(!gestionStocks){
        ui->groupConsoMvt->setVisible(false);
    }

    connect(ui->spinAgiosPourcentage,SIGNAL(valueChanged(int)),this,SLOT(majAgiosPgs()));
    connect(ui->spinAgiosSeuil,SIGNAL(valueChanged(double)),this,SLOT(majAgiosPgs()));

}

AdminWidget::~AdminWidget()
{
    delete ui;
}

void AdminWidget::on_buttonPgCreer_clicked()
{
    ui->groupPgCreer->show();
    ui->groupPgInfo->hide();
}

void AdminWidget::on_buttonPgClore_clicked()
{
    Pg pg(ui->linePgFams->text().toInt(),ui->listPgProms->currentItem()->text(),true);
    db.transaction();
    QSqlQuery q;
    q.prepare("UPDATE `Pg` SET `Actif` = :actif WHERE `Id` = :id");
    q.bindValue(":id",pg.getId());
    if(ui->buttonPgClore->text() == "Clore"){
        q.bindValue(":actif",0);
        ui->buttonPgClore->setText("Ouvrir");
    }else{
        q.bindValue(":actif",1);
        ui->buttonPgClore->setText("Clore");
    }
    q.exec();
    db.commit();

}

void AdminWidget::on_buttonPgValider_clicked()
{
    QString proms = ui->linePgNProms->text();

    int annee = proms.split(" ").at(1).toInt();
    int current = QDate::currentDate().year();
    if(annee + 1800 > current){
        QMessageBox::warning(this,tr("Promotion inexistante"),tr("Nous sommes en ")+QString::number(current)+tr(", la promotion que vous avez entré ne peut donc pas exister."));
    }
    if(!ui->linePgNBucque->text().isEmpty() && ui->linePgNProms->text().length() == 6)
    {
        db.transaction();
        QSqlQuery q;
        q.prepare("INSERT INTO `Pg` ( `Fams` , `Centre` , `Proms` , `Bucque` , `Actif`, `Statut`) VALUES ( :fams, :centre, :proms, :bucque, 1, :statut) ");
        q.bindValue(":fams",ui->spinPgNFams->value());
        QString p = ui->linePgNProms->text();
        QStringList l = p.split(" ");
        q.bindValue(":centre",l.at(0));
        q.bindValue(":proms",l.at(1).toInt());
        q.bindValue(":bucque",ui->linePgNBucque->text());
        q.bindValue(":statut",ui->comboPgNStatus->currentIndex());
        q.exec();

        qDebug() << q.lastError().text();
        db.commit();

        ui->linePgNBucque->setText("");
        ui->linePgNProms->setText("");
        ui->spinPgNFams->setValue(1);
        ui->groupPgCreer->hide();
        ui->groupPgInfo->show();
    }


}

void AdminWidget::on_buttonPgRetour_clicked()
{
    ui->linePgNBucque->setText("");
    ui->linePgNProms->setText("");
    ui->spinPgNFams->setValue(1);
    ui->groupPgCreer->hide();
    ui->groupPgInfo->show();
}

void AdminWidget::on_linePgFams_textEdited(QString text)
{
    ui->listPgProms->clear();

    ui->listPgProms->addItems(Pg::listProms(text.toInt(),true));

}

void AdminWidget::on_listPgProms_itemClicked(QListWidgetItem *it)
{
    Pg pg(ui->linePgFams->text().toInt(),it->text(),true);

    majInfosPg(pg);

    if(!ui->radioPgConsos->isEnabled()){
        ui->radioPgConsos->setEnabled(true);
        ui->radioPgRA->setEnabled(true);
    }

    actualiserListPgConsos(pg);

}

void AdminWidget::actualiserListPgConsos(Pg pg)
{
    int id = pg.getId();
    db.transaction();
    QSqlQuery q;

    if(ui->radioPgConsos->isChecked()){

        q.prepare("SELECT `h`.`Date`, `c`.`Intitule`, `c`.`Prix` FROM `Historique` AS `h` INNER JOIN `Consos` AS `c` ON `h`.`Conso` = `c`.`Id` WHERE `h`.`Pg` = :pg ORDER BY h.Date DESC");
        q.bindValue(":pg",id);
        q.exec();

        modelPgHistorique->setQuery(q);
        modelPgHistorique->setHeaderData(0, Qt::Horizontal, tr("Date"));
        modelPgHistorique->setHeaderData(1, Qt::Horizontal, tr("Intitulé"));
        modelPgHistorique->setHeaderData(2, Qt::Horizontal, tr("Prix"));


    }else{

        q.prepare("SELECT `r`.`Date`, `r`.`Somme`, `r`.`Num`, CONCAT(p.Bucque, \" \", p.Fams, \" \", p.centre, p.proms) FROM `Remise` AS `r` INNER JOIN `Pg` AS `p` ON `r`.`Zident` = `p`.`Id` WHERE `r`.`Pg` = :pg ORDER BY r.Date DESC");
        q.bindValue(":pg",id);
        q.exec();

        modelPgHistorique->setQuery(q);
        modelPgHistorique->setHeaderData(0, Qt::Horizontal, tr("Date"));
        modelPgHistorique->setHeaderData(1, Qt::Horizontal, tr("Somme"));
        modelPgHistorique->setHeaderData(2, Qt::Horizontal, tr("Numéro"));
        modelPgHistorique->setHeaderData(3, Qt::Horizontal, tr("Zident"));
    }

    ui->tablePgHistorique->setModel(modelPgHistorique);
    ui->tablePgHistorique->resizeColumnsToContents();
}

void AdminWidget::on_radioPgConsos_toggled(bool checked)
{
    Pg pg(ui->linePgFams->text().toInt(),ui->listPgProms->currentItem()->text(),true);
    actualiserListPgConsos(pg);
}


void AdminWidget::on_buttonPgModifier_clicked()
{
    if(ui->listPgProms->currentItem() != NULL){
        Pg pg(ui->linePgFams->text().toInt(),ui->listPgProms->currentItem()->text(),true);

        if(pg.getId() > 0){
            db.transaction();
            QSqlQuery q;
            q.prepare("UPDATE Pg SET Bucque = :bucque, Statut = :statut WHERE id = :id");
            q.bindValue(":id",pg.getId());
            q.bindValue(":bucque",ui->linePgBucque->text());
            q.bindValue(":statut",ui->comboPgStatus->currentIndex());
            q.exec();
            db.commit();
        }


        majInfosPg();
    }


}

void AdminWidget::on_linePgBucque_textEdited(const QString &arg1)
{
    ui->buttonPgModifier->setEnabled(true);
}


void AdminWidget::on_comboPgStatus_currentIndexChanged(int index)
{
    ui->buttonPgModifier->setEnabled(true);
}

void AdminWidget::majInfosPg(Pg p){
    Pg *pg;

    if(p.getId() == 0){
        Pg defaultPg(ui->linePgFams->text().toInt(),ui->listPgProms->currentItem()->text(),true);
        pg = &defaultPg;
    }else{
        pg = &p;
    }

    ui->linePgBucque->setText(pg->getBucque());
    ui->buttonPgModifier->setEnabled(false);

    ui->spinPgSolde->setValue(pg->getSolde());
    ui->comboPgStatus->setCurrentIndex(pg->getStatus());

    if(pg->isActif())
    {
        ui->buttonPgClore->setText("Clore");
    }else{
        ui->buttonPgClore->setText("Ouvrir");
    }


}

void AdminWidget::on_spinRAZidentFams_valueChanged(int i)
{
    ui->comboRAZidentProms->clear();
    ui->comboRAZidentProms->addItems(Pg::listProms(i));
}

void AdminWidget::on_buttonRemiseSession_clicked()
{
    ui->groupRemiseSession->show();
    if(tactile){
        ui->groupRemiseInfos->hide();
    }
}

void AdminWidget::on_buttonRemiseAnnuler_clicked()
{
    if(QMessageBox::question(this,"Annuler",tr("Voulez vous annuler la liste des chèques entrés ?"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
    {
        ui->groupRemiseSession->hide();
        ui->groupRemiseInfos->show();
        ui->lineRemiseBucque->setText("");
        ui->spinRemiseCheque->setValue(0);
        ui->spinRemiseSessionFams->setValue(0);
        ui->spinRemiseMontant->setValue(0.0);
        ui->spinRemiseSoldeFinal->setValue(0.0);
        ui->spinRemiseSoldeInit->setValue(0.0);
        ui->tableSessionRemise->clear();
        ui->tableSessionRemise->setRowCount(1);
    }
}

void AdminWidget::on_spinRemiseSessionFams_valueChanged(int i)
{
    ui->comboRemiseProms->clear();
    ui->comboRemiseProms->addItems(Pg::listProms(i));
}

void AdminWidget::on_comboRemiseProms_currentIndexChanged(const QString &arg1)
{

    Pg pg(ui->spinRemiseSessionFams->value(),arg1,true);
    ui->lineRemiseBucque->setText(pg.getBucque());
    ui->spinRemiseSoldeInit->setValue(pg.getSolde());
    ui->spinRemiseSoldeFinal->setValue(pg.getSolde() + ui->spinRemiseMontant->value());
}

void AdminWidget::on_spinRemiseMontant_valueChanged(double arg1)
{
    ui->spinRemiseSoldeFinal->setValue(ui->spinRemiseSoldeInit->value() + arg1);
}

void AdminWidget::on_buttonRemiseReset_clicked()
{
    if(QMessageBox::question(this,"R.A.Z",tr("Voulez vous remettre à zéro la liste des chèques entrés ?"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
    {
        ui->tableSessionRemise->clear();
        ui->tableSessionRemise->setRowCount(1);
    }
}

void AdminWidget::on_tableSessionRemise_itemClicked(QTableWidgetItem *item)
{
    int r = item->row();
    QTableWidgetItem *it = ui->tableSessionRemise->item(r,2);
    if(QMessageBox::question(this,"Annuler",tr("Voulez vous annuler le chèque numéro ")+ it->text() + tr(" ?"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
    {
        ui->tableSessionRemise->removeRow(r);
    }
}

void AdminWidget::on_buttonRemiseAjouter_clicked()
{
    Pg pg(ui->spinRemiseSessionFams->value(),ui->comboRemiseProms->currentText(),true);
    if(ui->spinRemiseCheque->value() > 0 && ui->spinRemiseMontant->value() > 0 && pg.getId() > 0){
        int s = ui->tableSessionRemise->rowCount();
        ui->tableSessionRemise->setRowCount(s+1);
        QTableWidgetItem *it0 = new QTableWidgetItem(ui->spinRemiseSessionFams->text());
        QTableWidgetItem *it1 = new QTableWidgetItem(ui->comboRemiseProms->currentText());
        QTableWidgetItem *it2 = new QTableWidgetItem(ui->spinRemiseCheque->text());
        QTableWidgetItem *it3 = new QTableWidgetItem(ui->spinRemiseMontant->text());

        ui->tableSessionRemise->setItem(s-1,0,it0);
        ui->tableSessionRemise->setItem(s-1,1,it1);
        ui->tableSessionRemise->setItem(s-1,2,it2);
        ui->tableSessionRemise->setItem(s-1,3,it3);
    }

    ui->lineRemiseBucque->setText("");
    ui->spinRemiseCheque->setValue(0);
    ui->spinRemiseSessionFams->setValue(0);
    ui->spinRemiseMontant->setValue(0.0);
    ui->spinRemiseSoldeFinal->setValue(0.0);
    ui->spinRemiseSoldeInit->setValue(0.0);
    ui->spinRemiseSessionFams->setFocus();

    ui->tableSessionRemise->resizeColumnsToContents();
}

void AdminWidget::on_buttonRemiseValider_clicked()
{
    Pg zi(ui->spinRAZidentFams->value(),ui->comboRAZidentProms->currentText(),true);

    int idPg = 0;
    int idZi = zi.getId();

    if(idZi > 0){
        db.transaction();
        QSqlQuery q;
        q.prepare("INSERT INTO `Remise` ( `Pg` , `Somme` , `Zident` , `Num` , `Date`) VALUES ( :Pg, :Somme, :Zident, :Num, NOW()) ");
        q.bindValue(":Zident",idZi);
        QSqlQuery q2;
        q2.prepare("UPDATE `Pg` SET `Solde` = `Solde` + :solde WHERE `Id` = :id");

        QList<int> pgs;
        QList<double> sommes;

        for(int i = 0;i < ui->tableSessionRemise->rowCount()-1;i++){
            Pg pg(ui->tableSessionRemise->item(i,0)->text().toInt(),ui->tableSessionRemise->item(i,1)->text(),true);
            idPg = pg.getId();
            if(idPg > 0){
                q.bindValue(":Pg",idPg);
                QStringList l = ui->tableSessionRemise->item(i,3)->text().split(' ');
                QString montant = l.at(0);
                montant.replace(",",".");
                q.bindValue(":Somme",montant.toDouble());
                q.bindValue(":Num",ui->tableSessionRemise->item(i,2)->text().toInt());
                q.exec();

                if(pgs.contains(idPg)){
                    double d = sommes.at(pgs.indexOf(idPg));
                    sommes.replace(pgs.indexOf(idPg),d + montant.toDouble());
                }else{
                    pgs << idPg;
                    sommes << montant.toDouble();
                }
            }

        }

        for(int j = 0;j < pgs.size(); j++){
            q2.bindValue(":solde",sommes.at(j));
            q2.bindValue(":id",pgs.at(j));
            q2.exec();
        }

        db.commit();

        ui->groupRemiseSession->hide();
        ui->groupRemiseInfos->show();
        ui->lineRemiseBucque->setText("");
        ui->spinRemiseCheque->setValue(0);
        ui->spinRemiseSessionFams->setValue(0);
        ui->spinRemiseMontant->setValue(0.0);
        ui->spinRemiseSoldeFinal->setValue(0.0);
        ui->spinRemiseSoldeInit->setValue(0.0);
        ui->tableSessionRemise->clear();
        ui->tableSessionRemise->setRowCount(1);
    }else{
        QMessageBox::warning(this,tr("Insuffisance des données"),tr("Le Zident-4H responsabe de la session n'est pas communiqué"));
    }

    majListeRemise();

}

void AdminWidget::majListeRemise()
{

    db.transaction();
    QSqlQuery q;
    q.prepare("SELECT t.Id, t.Date, t.PBucque, t.Num, t.Somme, CONCAT(z.Bucque, \" \", z.Fams, \" \", z.Centre, z.Proms) FROM (SELECT r.Id, r.Date, CONCAT(p.Bucque, \" \", p.Fams, \" \", p.Centre, p.Proms) AS PBucque, r.Zident, r.Num, r.Somme FROM Remise AS r INNER JOIN Pg AS p ON r.Pg = p.Id WHERE r.Num > 0) AS t INNER JOIN Pg As z ON t.Zident = z.Id");
    q.exec();

    modelRemise->setQuery(q);
    modelRemise->setHeaderData(0, Qt::Horizontal, tr("Id"));
    modelRemise->setHeaderData(1, Qt::Horizontal, tr("Date"));
    modelRemise->setHeaderData(2, Qt::Horizontal, tr("Pg"));
    modelRemise->setHeaderData(3, Qt::Horizontal, tr("Numéro"));
    modelRemise->setHeaderData(4, Qt::Horizontal, tr("Somme"));
    modelRemise->setHeaderData(5, Qt::Horizontal, tr("Zident"));

    ui->tableRemises->resizeColumnsToContents();
}

void AdminWidget::on_buttonRemiseSupprimer_clicked()
{

    int id = modelRemise->data(modelRemise->index(ui->tableRemises->currentIndex().row(),0)).toInt();
    QString numero = modelRemise->data(modelRemise->index(ui->tableRemises->currentIndex().row(),3)).toString();
    if(QMessageBox::question(this,"Supprimer",tr("Voulez vous annuler le chèque numéro ")+ numero,QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
    {
        db.transaction();
        QSqlQuery q;

        q.prepare("SELECT Pg FROM Remise WHERE Id = :id");
        q.bindValue(":id",id);
        q.exec();
        q.first();
        int idPg = q.value(0).toInt();

        q.prepare("UPDATE Pg SET Solde = Solde - (SELECT Somme FROM Remise WHERE Id = :remise) WHERE Id = :id");
        q.bindValue(":id",idPg);
        q.bindValue(":remise",id);
        q.exec();


        q.prepare("DELETE FROM Remise WHERE Id = :id");
        q.bindValue(":id",id);
        q.exec();

        db.commit();
    }
    majListeRemise();
}

void AdminWidget::on_buttonAgiosNouveau_clicked()
{
    ui->groupAgiosAjouter->show();
    if(tactile){
        ui->groupAgiosInfos->hide();
    }
}

void AdminWidget::on_buttonAgiosRetour_clicked()
{
    ui->spinAgiosPourcentage->setValue(0);
    ui->spinAgiosSeuil->setValue(0.0);
    ui->groupAgiosAjouter->hide();
    ui->groupAgiosInfos->show();
    modelAgiosPgs->query().finish();

}

void AdminWidget::majListAgios(QStringList l)
{

    db.transaction();
    QSqlQuery q;

    if(l.count() != 3){
        q.prepare("SELECT t.Id, t.Date, t.PBucque, t.Somme, CONCAT(z.Bucque, \" \", z.Fams, \" \", z.Centre, z.Proms) FROM (SELECT r.Id, r.Date, CONCAT(p.Bucque, \" \", p.Fams, \" \", p.Centre, p.Proms) AS PBucque, r.Zident, r.Somme FROM Remise AS r INNER JOIN Pg AS p ON r.Pg = p.Id WHERE r.Num = 0) AS t INNER JOIN Pg As z ON t.Zident = z.Id ORDER BY t.Id");
        q.exec();
    }else{
        q.prepare("SELECT t.Id, t.Date, t.PBucque, t.Somme, CONCAT(z.Bucque, \" \", z.Fams, \" \", z.Centre, z.Proms) FROM (SELECT r.Id, r.Date, CONCAT(p.Bucque, \" \", p.Fams, \" \", p.Centre, p.Proms) AS PBucque, r.Zident, r.Somme FROM Remise AS r INNER JOIN Pg AS p ON r.Pg = p.Id WHERE r.Num = 0 AND p.Fams = :f AND p.Centre = :c AND p.Proms = :p) AS t INNER JOIN Pg As z ON t.Zident = z.Id ORDER BY t.Id");
        q.bindValue(":f",l[0].toInt());
        q.bindValue(":c",l[1]);
        q.bindValue(":p",l[2].toInt());
        q.exec();
    }




    modelAgios->setQuery(q);
    modelAgios->setHeaderData(0, Qt::Horizontal, tr("Id"));
    modelAgios->setHeaderData(1, Qt::Horizontal, tr("Date"));
    modelAgios->setHeaderData(2, Qt::Horizontal, tr("Pg"));
    modelAgios->setHeaderData(3, Qt::Horizontal, tr("Somme"));
    modelAgios->setHeaderData(4, Qt::Horizontal, tr("Zident"));

    ui->tableAgiosInfo->resizeColumnsToContents();
}


void AdminWidget::on_buttonAgiosSupprimer_clicked()
{
    int id = modelAgios->data(modelAgios->index(ui->tableAgiosInfo->currentIndex().row(),0)).toInt();
    QString pg = modelAgios->data(modelAgios->index(ui->tableAgiosInfo->currentIndex().row(),2)).toString();
    QString date = modelAgios->data(modelAgios->index(ui->tableAgiosInfo->currentIndex().row(),1)).toString();
    if(id > 0 && QMessageBox::question(this,"Supprimer",tr("Voulez vous annuler les Agios de ")+ pg + tr(" en date du ") + date + tr(" ?"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
    {
        db.transaction();
        QSqlQuery q;

        q.prepare("UPDATE Pg SET Solde = Solde - (SELECT Somme FROM Remise WHERE Id = :remise) WHERE Id = (SELECT Pg FROM Remise WHERE Id = :remise2)");
        q.bindValue(":remise2",id);
        q.bindValue(":remise",id);
        q.exec();

        q.prepare("SELECT Pg, Zident, 0 - Somme FROM Remise WHERE Id = :id");
        q.bindValue(":id",id);
        q.exec();
        q.first();
        int idPg = q.value(0).toInt();
        int idZi = q.value(1).toInt();
        double somme = q.value(2).toDouble();

        q.prepare("INSERT INTO `Remise`(`Pg`, `Somme`, `Zident`, `Num`, `Date`) VALUES (:pg , :somme, :zident, 0, NOW())");
        q.bindValue(":pg",idPg);
        q.bindValue(":zident",idZi);
        q.bindValue(":somme",somme);
        q.exec();

        db.commit();
    }

    majListAgios();
}

void AdminWidget::majAgiosPgs()
{

    QString s = "";

    for(int i = 0; i< selectionModel->selectedIndexes().size(); i++){
        s += "'";
        s += selectionModel->selectedIndexes().at(i).data().toString();
        s += "'";
        if(i < selectionModel->selectedIndexes().size() - 1){
            s += ",";
        }
    }


    db.transaction();
    QSqlQuery q;
    q.prepare("SELECT CONCAT(Bucque, \" \", Fams, \" \", Centre, Proms), - ABS(Solde) * :percent / 100, Solde * (100 + :percent2) /100 FROM Pg WHERE Solde < :seuil AND Actif = 1 AND CONCAT(Centre, \" \", Proms) IN (" + s + ")");
    q.bindValue(":percent",ui->spinAgiosPourcentage->value());
    q.bindValue(":percent2",ui->spinAgiosPourcentage->value());
    q.bindValue(":seuil",ui->spinAgiosSeuil->value());
    q.exec();

    modelAgiosPgs->setQuery(q);
    modelAgiosPgs->setHeaderData(0,Qt::Horizontal,tr("Pg"));
    modelAgiosPgs->setHeaderData(1,Qt::Horizontal,tr("Somme prélevée"));
    modelAgiosPgs->setHeaderData(2,Qt::Horizontal,tr("Nouveau solde"));

}

void AdminWidget::majAgiosProms()
{
    db.transaction();
    QSqlQuery q;
    q.prepare("SELECT CONCAT(Centre, \" \", Proms) AS p FROM Pg WHERE Actif = 1 GROUP BY p");
    q.exec();

    modelAgiosProms->setQuery(q);

    selectionModel = ui->listAgiosProms->selectionModel();
    selectionModel->clearSelection();
}

void AdminWidget::on_listAgiosProms_clicked(const QModelIndex &index)
{
    if(selectionModel->isSelected(index))
    {
        selectionModel->select(index,QItemSelectionModel::Deselect);
    }else{
        selectionModel->select(index,QItemSelectionModel::Select);
    }

    majAgiosPgs();
}

void AdminWidget::on_buttonAgiosAppliquer_clicked()
{
    Pg zi(ui->spinRAZidentFams->value(),ui->comboRAZidentProms->currentText(),true);
    int idZi = zi.getId();

    if(idZi > 0)
    {
        if(QMessageBox::question(this,"Agios",tr("Voulez vous appliquer ces agios ?"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
        {

            QStringList l;
            for(int i = 0; i< selectionModel->selectedIndexes().size(); i++){
                l.append("'"+selectionModel->selectedIndexes().at(i).data().toString()+"'");
            }
            QString s = l.join(",");

            db.transaction();
            QSqlQuery q;
            q.prepare("SELECT Id FROM Pg WHERE Solde < :seuil AND Actif = 1 AND CONCAT(Centre, \" \", Proms) IN (" + s + ")");
            q.bindValue(":seuil",ui->spinAgiosSeuil->value());
            q.exec();


            QSqlQuery q2;
            q2.prepare("INSERT INTO Remise (Pg, Somme, Zident, Num, Date) VALUES (:pg, (SELECT  - ABS(Solde) * :percent / 100 FROM Pg WHERE Id = :id), :zident, 0, NOW())");
            q2.bindValue(":zident",idZi);

            QSqlQuery q3;
            q3.prepare("UPDATE Pg SET Solde = Solde + (SELECT Somme FROM Remise WHERE Id = LAST_INSERT_ID()) WHERE Id = :id");

            while(q.next()){
                q2.bindValue(":pg",q.value(0));
                q2.bindValue(":id",q.value(0));
                q2.bindValue(":percent",ui->spinAgiosPourcentage->value());
                q2.exec();

                q3.bindValue(":id",q.value(0));
                q3.exec();

            }


            db.commit();

            on_buttonAgiosRetour_clicked();
            majListAgios();

        }
    }else{
        QMessageBox::warning(this,tr("Insuffisance des données"),tr("Le Zident-4H responsabe de la session n'est pas communiqué"));
    }
}

void AdminWidget::on_buttonConsosAjouter_clicked()
{
    QString mot = MdpDialog::mot(this,"Mot Clé","Entrez le nouveau mot clé");
    if(ui->listConsosMotsClefs->findItems(mot,Qt::MatchContains).size() == 0){
        ui->listConsosMotsClefs->addItem(mot);
    }else{
        QMessageBox::information(this,"Inutile","Un des mots clés contient déjà le texte que vous voulez ajouter");
    }
    if(verifieIntitule()){
        ui->buttonConsosModifier->setEnabled(true);
    }
}

void AdminWidget::on_buttonConsosNouveau_clicked()
{
    QString nom = ui->lineConsosInfosIntitule->text();

    db.transaction();
    QSqlQuery q;
    q.prepare("SELECT Id FROM Consos WHERE Intitule = :int AND Actif = 1");
    q.bindValue(":int",nom);
    q.exec();

    if(verifieIntitule() && !q.first()){

            QString mc = "";
            if(ui->listConsosMotsClefs->count() > 0){
                mc = ui->listConsosMotsClefs->item(0)->text();
                for(int i = 1; i < ui->listConsosMotsClefs->count(); i++){
                    if(ui->listConsosMotsClefs->item(i)->text() != ""){
                        mc += ";";
                        mc += ui->listConsosMotsClefs->item(i)->text();
                    }
                }
            }

            db.transaction();
            QSqlQuery q2;
            q2.prepare("INSERT INTO Consos (Intitule, MotsClefs, Actif, Prix, Dispo, Reserve) VALUES(:intitule,:motsclefs,1,:prix,0,0)");
            q2.bindValue(":intitule",nom);
            q2.bindValue(":motsclefs",mc);
            q2.bindValue(":prix",ui->spinConsosInfoPrix->value());
            q2.exec();
            db.commit();

            modelConsos->select();
            ui->lineConsosInfosIntitule->setText("");
            ui->spinConsosInfoPrix->setValue(0.0);
            ui->listConsosMotsClefs->clear();

    }
    ui->buttonConsosNouveau->setEnabled(false);
}


void AdminWidget::on_listConsosMotsClefs_itemClicked(QListWidgetItem *item)
{
    ui->listConsosMotsClefs->takeItem(ui->listConsosMotsClefs->currentRow());
    if(idConsoCourante > 0 && verifieIntitule()){
        ui->buttonConsosModifier->setEnabled(true);
    }
}

void AdminWidget::on_buttonConsosModifier_clicked()
{
    if(verifieIntitule()){
        QString mc = ui->listConsosMotsClefs->item(0)->text();
        for(int i = 1; i<ui->listConsosMotsClefs->count(); i++){
            mc += ";";
            mc += ui->listConsosMotsClefs->item(i)->text();
        }

        db.transaction();
        QSqlQuery q;
        q.prepare("SELECT Prix, Dispo, Reserve FROM Consos WHERE Id = :id");
        q.bindValue(":id",idConsoCourante);
        q.exec();
        q.first();

        if(q.value(0).toDouble() != ui->spinConsosInfoPrix->value()){
            if(QMessageBox::question(this,"Modification",tr("Attention le prix de la conso a été modifié! Pour des raisons de pérénité, une nouvelle conso sera créée. N'en abuser pas, cela remplie vite une base."),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
            {
                QSqlQuery q2;
                q2.prepare("INSERT INTO Consos (Intitule, MotsClefs, Actif, Prix, Dispo, Reserve) VALUES(:intitule,:motsclefs,1,:prix,:dispo,:reserve)");
                q2.bindValue(":intitule",ui->lineConsosInfosIntitule->text());
                q2.bindValue(":motsclefs",mc);
                q2.bindValue(":prix",ui->spinConsosInfoPrix->value());
                q2.bindValue(":dispo",q.value(1).toInt());
                q2.bindValue(":reserve",q.value(2).toInt());

                q.prepare("UPDATE Consos SET Actif = 0, MotsClefs = \"\", Dispo = 0, Reserve = 0 WHERE Id = :id");
                q.bindValue(":id",idConsoCourante);
                q.exec();

                q2.exec();
                idConsoCourante = q2.lastInsertId().toInt();
                q.finish();
            }
        }else{
            if(QMessageBox::question(this,"Modification",tr("Voulez vous modifier les infos de cette conso ?"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok)
            {
                q.finish();
                q.prepare("UPDATE Consos SET Intitule = :intitule, MotsClefs = :motsclefs WHERE Id = :id");
                q.bindValue(":intitule",ui->lineConsosInfosIntitule->text());
                q.bindValue(":motsclefs",mc);
                q.bindValue(":id",idConsoCourante);
                q.exec();
            }
        }
        q.finish();
        db.commit();

        modelConsos->select();
    }
    ui->buttonConsosModifier->setEnabled(false);
}

void AdminWidget::on_lineConsosInfosIntitule_textEdited(const QString &arg1)
{

    if(verifieIntitule())
    {
        ui->buttonConsosNouveau->setEnabled(true);
        if(idConsoCourante > 0){
            ui->buttonConsosModifier->setEnabled(true);
        }
    }else{
        ui->buttonConsosModifier->setEnabled(false);
        ui->buttonConsosNouveau->setEnabled(false);
    }
}

void AdminWidget::on_spinConsosInfoPrix_editingFinished()
{
    if(idConsoCourante > 0){
        ui->buttonConsosModifier->setEnabled(true);
    }
}

void AdminWidget::on_listConsos_clicked(const QModelIndex &index)
{

    db.transaction();
    QSqlQuery q;
    q.prepare("SELECT * FROM Consos WHERE Intitule = :intitule AND Actif = 1");
    q.bindValue(":intitule",modelConsos->index(index.row(),1).data().toString());
    q.exec();
    if(q.first()){
        ui->lineConsosInfosIntitule->setText(q.value(1).toString());
        ui->spinConsosInfoPrix->setValue(q.value(4).toDouble());
        QStringList l = q.value(2).toString().split(";");
        ui->listConsosMotsClefs->clear();
        ui->listConsosMotsClefs->addItems(l);
        ui->spinConsosDispo->setValue(q.value(5).toInt());
        ui->spinConsosReserve->setValue(q.value(6).toInt());
        idConsoCourante = q.value(0).toInt();
    }
    ui->buttonConsosModifier->setEnabled(false);
    q.finish();
    db.commit();

    majStat();
}

void AdminWidget::on_lineConsosRecherche_textChanged(const QString &arg1)
{
    modelConsos->setFilter("Actif = 1 AND Intitule LIKE '%"+arg1+"%'");
    modelConsos->select();
}

void AdminWidget::on_buttonConsosMvtDispo_clicked()
{
    if(idConsoCourante > 0){
        int restante = MvtDialog::restant(this);
        int mvt = ui->spinConsosReserve->value() + 1;
        while(mvt > ui->spinConsosReserve->value() || mvt < 0 - restante)
        {
            mvt = MvtDialog::mvt(this);
        }

        db.transaction();
        QSqlQuery q;
        q.prepare("INSERT INTO Biroute (Conso, Quantite, Date) VALUES (:conso, (SELECT Dispo FROM Consos WHERE Id = :id) - :rest , NOW())");
        q.bindValue(":conso",idConsoCourante);
        q.bindValue(":id",idConsoCourante);
        q.bindValue(":rest",restante);
        q.exec();

        q.prepare("UPDATE Consos SET Dispo = :qte , Reserve = Reserve - :res WHERE Id = :id");
        q.bindValue(":id",idConsoCourante);
        q.bindValue(":qte",mvt + restante);
        q.bindValue(":res",mvt);
        q.exec();

        db.commit();
    }

    majInfosConsos();

}

void AdminWidget::on_buttonConsosMvtReserve_clicked()
{
    if(idConsoCourante > 0){

        int mvt = 0 - ui->spinConsosReserve->value() - 1;
        while(mvt < 0 - ui->spinConsosReserve->value())
        {
              mvt = MvtDialog::mvt(this);
        }

        db.transaction();
        QSqlQuery q;
        q.prepare("UPDATE Consos SET Reserve = Reserve + :res WHERE Id = :id");
        q.bindValue(":id",idConsoCourante);
        q.bindValue(":res",mvt);
        q.exec();
        db.commit();
    }
    majInfosConsos();
}

void AdminWidget::majInfosConsos(){
    if(idConsoCourante > 0){
        db.transaction();
        QSqlQuery q;
        q.prepare("SELECT * FROM Consos WHERE Id = :id");
        q.bindValue(":id",idConsoCourante);
        q.exec();
        if(q.first()){
            ui->lineConsosInfosIntitule->setText(q.value(1).toString());
            ui->spinConsosInfoPrix->setValue(q.value(4).toDouble());
            QStringList l = q.value(2).toString().split(";");
            ui->listConsosMotsClefs->clear();
            ui->listConsosMotsClefs->addItems(l);
            ui->spinConsosDispo->setValue(q.value(5).toInt());
            ui->spinConsosReserve->setValue(q.value(6).toInt());
            idConsoCourante = q.value(0).toInt();
        }
        q.finish();
        db.commit();
    }else{

        ui->lineConsosInfosIntitule->setText("");
        ui->spinConsosInfoPrix->setValue(0.0);
        ui->listConsosMotsClefs->clear();
        ui->spinConsosDispo->setValue(0);
        ui->spinConsosReserve->setValue(0);
    }
}

bool AdminWidget::verifieIntitule()
{

    QString nom = ui->lineConsosInfosIntitule->text();

    return nom != "" && !nom.contains(" x ") && !nom.contains(" : ");


}

void AdminWidget::on_buttonAdminQuitter_clicked()
{
    qApp->quit();
}

void AdminWidget::on_buttonAdminMdp_clicked()
{
    QSettings s;
    if(MdpDialog::mdp(this,s.value("pass").toString())){
        QString pass = "";
        while(pass == ""){
            pass = MdpDialog::nouveau(this);
        }
        s.setValue("pass",pass);
    }

}

void AdminWidget::on_buttonConsoStat_clicked()
{
    ui->groupConsosStat->show();

    ui->groupConsosModifier->hide();
}

void AdminWidget::on_buttonStatFermer_clicked()
{
    ui->groupConsosModifier->show();
    ui->groupConsosStat->hide();
}

void AdminWidget::majStat()
{
    bool biroute = ui->radioConsosTypeBiroute->isChecked();
    bool duree = ui->radioConsosDureeAnnee->isChecked();
    db.transaction();
    QSqlQuery q;
    QString requete = "SELECT COUNT(*) AS nb, ";
    if(duree){
        requete += "WEEK(Date)";
    }else{
        requete += "DAY(Date)";
    }
    requete += " As d FROM ";
    if(biroute){
        requete += "Biroute";
    }else{
        requete += "Historique";
    }
    requete += " WHERE Conso = :id AND DATE(Date) >= STR_TO_DATE('"+ui->dateConsoDu->date().toString(Qt::ISODate)+"','%Y-%m-%d') AND DATE(Date) < STR_TO_DATE('"+ui->dateConsoAu->date().toString(Qt::ISODate)+"','%Y-%m-%d') GROUP BY d";
    q.prepare(requete);
    q.bindValue(":id",idConsoCourante);
    q.exec();

    QList<double> qtes;
    //QList<int> dates;

    QDate baseDate = ui->dateConsoDu->date();

    int nbJours = duree ? 7 : 1;

    if(biroute){
        while(baseDate < ui->dateConsoAu->date()){
            if(q.next()){
                if(duree){
                    if(baseDate.weekNumber() != q.value(1).toInt()){
                        int diff = baseDate.weekNumber() < q.value(1).toInt() ? q.value(1).toInt() - baseDate.weekNumber() + 1 : QDate(31,12,baseDate.year()).weekNumber() + baseDate.weekNumber() + 1 - q.value(1).toInt();
                        double tmp = q.value(0).toDouble() / diff ;
                        for(int i=0;i < diff;i++)
                        {
                            qtes << tmp;

                        }
                        baseDate = baseDate.addDays((diff-1)*7);
                    }else{
                        qtes << q.value(0).toDouble();
                    }
                }else{
                    if(baseDate.day() != q.value(1).toInt()){
                        int diff = baseDate.day() < q.value(1).toInt() ? baseDate.daysTo(QDate(q.value(1).toInt(),baseDate.month(),baseDate.year())) + 1 : baseDate.daysTo(QDate(q.value(1).toInt(),baseDate.addMonths(1).month(),baseDate.addMonths(1).year())) + 1;
                        double tmp = q.value(0).toDouble() / diff ;
                        for(int i=0;i < diff;i++)
                        {
                            qtes << tmp;

                        }
                        baseDate = baseDate.addDays(diff-1);
                    }else{
                        qtes << q.value(0).toDouble();
                    }
                }
            }else{
                qtes << 0.0;
            }
            baseDate = baseDate.addDays(nbJours);
        }
    }else{
        while(baseDate < ui->dateConsoAu->date()){
            if(q.next()){
                if(duree){
                    while(baseDate.weekNumber() != q.value(1).toInt()){
                        qtes << 0.0;
                        baseDate = baseDate.addDays(nbJours);
                    }
                    qtes << q.value(0).toDouble();
                }else{
                    while(baseDate.day() != q.value(1).toInt()){
                        qtes << 0.0;
                        baseDate = baseDate.addDays(nbJours);
                    }
                    qtes << q.value(0).toDouble();
                }
            }else{
                qtes << 0.0;
            }
            baseDate = baseDate.addDays(nbJours);
        }
    }


    scene->clear();
    ui->graphicsView->resetTransform();

    QListIterator<double> it(qtes);
    //QListIterator<int> it2(dates);

    int position = 0;
    double hauteur = 0.0;
    while(it.hasNext()){
        double qte = it.next();
        //int date = it2.next();
        scene->addRect(position,0,50,-20*qte);
        QGraphicsTextItem *item = new QGraphicsTextItem(QString::number(qte));
        item->setPos(position,0);
        scene->addItem(item);
        if(20*qte > hauteur)
        {
            hauteur = 20*qte;
        }
        position += 50;
    }

    ui->graphicsView->scale(double(ui->graphicsView->width()-30)/position,double(ui->graphicsView->height()-30)/(hauteur*2));

}

void AdminWidget::majDuree()
{
    QDateEdit *e;
    QDate base;

    int signe = 0;

    if(dernierModifier != NULL){
        base = dernierModifier->date();
        if(dernierModifier->objectName() == "dateConsoAu"){
            e = ui->dateConsoDu;
            signe = -1;
        }else{
            e = ui->dateConsoAu;
            signe = 1;
        }
    }else{
        base = QDate::currentDate();
        dernierModifier = ui->dateConsoAu;
        dernierModifier->setDate(base);
        e = ui->dateConsoDu;
        signe = -1;
    }

    if(ui->radioConsosDureeMois->isChecked()){
        e->setDate(base.addMonths(signe));
    }else{
        e->setDate(base.addYears(signe));
    }

    majStat();

}

void AdminWidget::on_dateConsoDu_editingFinished()
{
    dernierModifier = ui->dateConsoDu;
    majDuree();
}

void AdminWidget::on_dateConsoAu_editingFinished()
{
    dernierModifier = ui->dateConsoAu;
    majDuree();
}

void AdminWidget::on_radioConsosDureeMois_toggled(bool checked)
{
    majDuree();
}

void AdminWidget::on_radioConsosTypeConso_toggled(bool checked)
{
    majStat();
}

void AdminWidget::on_buttonAdminRetour_clicked()
{
    close();
}

void AdminWidget::on_plainTextEdit_textChanged()
{
    QSettings s;
    s.setValue("message",ui->plainTextEdit->toPlainText());
}

void AdminWidget::on_checkAdminTactile_clicked(bool checked)
{
    QString mot = checked ? "OUI" : "NON";
    QSettings s;
    s.setValue("tactile",mot);
}

void AdminWidget::on_checkAdminStock_clicked(bool checked)
{
    QString mot = checked ? "OUI" : "NON";
    QSettings s;
    s.setValue("gestionstock",mot);
}

void AdminWidget::on_buttonAdminAppliquer_clicked()
{
    QStringList dispo;
    for(int i = 0; i< ui->listAdminDispo->count(); i++){
        if(ui->listAdminDispo->item(i)->text() != "")
            dispo.append(ui->listAdminDispo->item(i)->text());
    }
    QStringList droite;
    for(int i = 0; i< ui->listAdminDroite->count(); i++){
        if(ui->listAdminDroite->item(i)->text() != "")
            droite.append(ui->listAdminDroite->item(i)->text());
    }
    QStringList gauche;
    for(int i = 0; i< ui->listAdminGauche->count(); i++){
        if(ui->listAdminGauche->item(i)->text() != "")
            gauche.append(ui->listAdminGauche->item(i)->text());
    }

    QSettings s;
    s.setValue("prefBlocDroite",droite.join(";"));
    s.setValue("prefBlocGauche",gauche.join(";"));
    s.sync();

    emit changementAccueil();

}

void AdminWidget::on_buttonConsosSupprimer_clicked()
{
    if(idConsoCourante>0){
        if(QMessageBox::question(this,"Suppression",tr("Attention la consos sera supprimée de la liste mais pas de la base (pour des raisons de perénités)"),QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Ok) == QMessageBox::Ok){
            db.transaction();
            QSqlQuery q;
            q.prepare("UPDATE Consos SET Actif = 0, MotsClefs = \"\", Dispo = 0, Reserve = 0 WHERE Id = :id");
            q.bindValue(":id",idConsoCourante);
            q.exec();
            db.commit();
            idConsoCourante = 0;
            majInfosConsos();

            modelConsos->select();
        }
    }
}

void AdminWidget::on_buttonMBImporter_clicked()
{


    DialogImporter *d = new DialogImporter();
    d->exec();

    emit changementAccueil();


}

void AdminWidget::on_buttonAdminTransfert_clicked()
{
    DialogTransfert *d = new DialogTransfert();
    d->exec();
}

void AdminWidget::on_spinAgiosRecherche_valueChanged(int arg1)
{
    if(arg1 >0){
        ui->comboAgiosRecherche->clear();
        ui->comboAgiosRecherche->addItem("Aucun");
        ui->comboAgiosRecherche->addItems(Pg::listProms(arg1));
    }else{
        ui->comboAgiosRecherche->clear();
        ui->comboAgiosRecherche->addItem("Aucun");
    }

}

void AdminWidget::on_comboAgiosRecherche_currentTextChanged(const QString &arg1)
{
    if(arg1 != "Aucun" && arg1 != ""){
        QStringList l = arg1.split(" ");
        l.prepend(QString::number(ui->spinAgiosRecherche->value()));
        qDebug() << arg1;
        majListAgios(l);


    }else{
        majListAgios();
    }
}

void AdminWidget::on_pushButton_clicked()
{
    QSettings s;
    s.setValue("connexion/hostname",ui->lineConnexionHost->text());
    s.setValue("connexion/dbname",ui->lineConnexionBase->text());
    s.setValue("connexion/user",ui->lineConnexionUser->text());
    s.setValue("connexion/pw",ui->lineConnexionPw->text());
    s.sync();
    emit reconnecterBase();
}
